﻿using System;
using System.ComponentModel;
using System.Data.SqlClient;
using QLHS.DTO;

namespace QLHS.DataLayer
{
    public class DiemDAO
    {
        public BindingList<DiemDTO> LoadAll()
        {
            SqlConnection cn = new SqlConnection(Helper.ConnectionString);
            string sql = "select* from BANGDIEM";
            BindingList<DiemDTO> DiemTable = new BindingList<DiemDTO>();

            try
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int _ID = Convert.ToInt32(reader["ID"].ToString());
                        int _MSMH = Convert.ToInt32(reader["MaSoMH"].ToString());
                        int _MSHK = Convert.ToInt32(reader["MaSoHK"].ToString());
                        double _MuoiLamPhut = Convert.ToDouble(reader["MuoiLamPhut"].ToString());
                        double _MotTiet = Convert.ToDouble(reader["MotTiet"].ToString());
                        double _CuoiKy = Convert.ToDouble(reader["CuoiKy"].ToString());
                        double _TBMH = Convert.ToDouble(reader["TBMH"].ToString());

                        DiemDTO temp = new DiemDTO
                        {
                            ID = _ID,
                            MaSoMH = _MSMH,
                            MaSoHK = _MSHK,
                            MuoiLamPhut = _MuoiLamPhut,
                            MotTiet = _MotTiet,
                            CuoiKy = _CuoiKy,
                            TBMH = _TBMH
                        };
                        DiemTable.Add(temp);
                    }
                }
            }
            finally
            {
                if (cn != null && cn.State == System.Data.ConnectionState.Open)
                {
                    cn.Close();
                }
            }
            return DiemTable;
        }

        public void Delete(DiemDTO del)
        {
            string sql = string.Format("delete from BANGDIEM where ID = {0} and MaSoMH = {1}, MaSoHK = {2}", del.ID, del.MaSoMH, del.MaSoHK);
            SqlConnection cn = new SqlConnection(Helper.ConnectionString);

            try
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                cmd.ExecuteNonQuery();
            }
            finally
            {
                if (cn != null && cn.State == System.Data.ConnectionState.Open)
                {
                    cn.Close();
                }
            }         
        }

        public void Insert(DiemDTO InsertObject)
        {
            string sql = string.Format("insert into BANGDIEM  values ({0}, {1}, {2}, {3}, {4}, {5}, {6})",
               InsertObject.ID, InsertObject.MaSoMH, InsertObject.MaSoHK, InsertObject.MuoiLamPhut, InsertObject.MotTiet, InsertObject.CuoiKy, InsertObject.TBMH);
            SqlConnection cn = new SqlConnection(Helper.ConnectionString);
            try
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                cmd.ExecuteNonQuery();
            }
            finally
            {
                if (cn != null && cn.State == System.Data.ConnectionState.Open)
                {
                    cn.Close();
                }
            }
        }

        public void Update(DiemDTO UpdateObject)
        {
            string sql = string.Format("update BANGDIEM set MuoiLamPhut = {3} , MotTiet = {4} , CuoiKy = {5} , TBMH = {6} where ID = {0} and MaSoMH = {1} and MaSoHK = {2}", 
                UpdateObject.ID, UpdateObject.MaSoMH, UpdateObject.MaSoHK, UpdateObject.MuoiLamPhut, UpdateObject.MotTiet, UpdateObject.CuoiKy, UpdateObject.TBMH);

            SqlConnection cn = new SqlConnection(Helper.ConnectionString);
            try
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                cmd.ExecuteNonQuery();
            }
            finally
            {
                if (cn != null && cn.State == System.Data.ConnectionState.Open)
                {
                    cn.Close();
                }
            }
        }

        //public void ChangeKeyValue(string Old_MSHS, string New_MSHS, string Old_MSMH, string New_MSMH)
        //{
        //    string sql = string.Format("update BANGDIEM set MSHS = '{0}' , MSMH = '{1}' where (MSHS = '{2}' and MSMH = '{3}')", New_MSHS, New_MSMH, Old_MSHS, Old_MSMH);

        //    SqlConnection cn = new SqlConnection(Helper.ConnectionString);
        //    SqlCommand cmd = new SqlCommand(sql, cn);
        //    cn.Open();
        //    cmd.ExecuteNonQuery();
        //    cn.Close();
        //}

        public BindingList<DiemDTO> GetDiemDataByPrimaryKey(int ID, int MSMH , int MSHK)
        {
            SqlConnection cn = new SqlConnection(Helper.ConnectionString);
            string sql = string.Format("select* from BANGDIEM where ID = {0} and MaSoMH = {1} and MaSoHK = {2}", ID, MSMH, MSHK);       
            BindingList<DiemDTO> Result = new BindingList<DiemDTO>();
            try
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int _ID = Convert.ToInt32(reader["ID"].ToString());
                        int _MSMH = Convert.ToInt32(reader["MaSoMH"].ToString());
                        int _MSHK = Convert.ToInt32(reader["MaSoHK"].ToString());
                        double _MuoiLamPhut = Convert.ToDouble(reader["MuoiLamPhut"].ToString());
                        double _MotTiet = Convert.ToDouble(reader["MotTiet"].ToString());
                        double _CuoiKy = Convert.ToDouble(reader["CuoiKy"].ToString());
                        double _TBMH = Convert.ToDouble(reader["TBMH"].ToString());

                        DiemDTO temp = new DiemDTO
                        {
                            ID = _ID,
                            MaSoMH = _MSMH,
                            MaSoHK = _MSHK,
                            MuoiLamPhut = _MuoiLamPhut,
                            MotTiet = _MotTiet,
                            CuoiKy = _CuoiKy,
                            TBMH = _TBMH
                        };
                        Result.Add(temp);
                    }
                }
            }
            finally
            {
                if (cn != null && cn.State == System.Data.ConnectionState.Open)
                {
                    cn.Close();
                }
            }

            return Result;
        }
    }
}
